﻿/*
 *  Source code courtesy of the desktopWeb.CodePlex.com community project. See MS-PL license on Codeplex.com. 
 *  This is beta code not intended for a production application.
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.SharePoint.Client;
using eb = MyData.ExpressionBuilder.Model;
using DocumentFormat.OpenXml.Wordprocessing;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Data;

namespace MyData.Activities
{
  public static class Common
  {
    
    public static void CopyStream(Stream source, Stream destination)
    {
      //Temporarily hard-coded value
      byte[] buffer = new byte[32768];
      int bytesRead;
      do
      {
        bytesRead = source.Read(buffer, 0, buffer.Length);
        destination.Write(buffer, 0, bytesRead);
      } while (bytesRead != 0);
    }

    public static Stream FileStream(string url, string documentName, string libraryTitle)
    {
      Stream fileStream = null;
      Dictionary<string, object> fileMetadata = new Dictionary<string, object>();
      FileInformation fileInformation = null;

      using (ClientContext clientContext = new ClientContext(url))
      {
        //WF: clientContext.Credentials = NetworkCredential.Get(context);
        Microsoft.SharePoint.Client.List sharedDocumentsList = clientContext.Web.Lists.GetByTitle(libraryTitle);

        CamlQuery camlQuery = new CamlQuery();
        camlQuery.ViewXml =
          @"<View>
              <Query>
                <Where>
                  <Eq>
                    <FieldRef Name='FileLeafRef'/>
                    <Value Type='Text'>" + documentName + @"</Value>
                  </Eq>
                </Where>
                <RowLimit>1</RowLimit>
              </Query>
            </View>";
        Microsoft.SharePoint.Client.ListItemCollection listItems = sharedDocumentsList.GetItems(camlQuery);
        clientContext.Load(sharedDocumentsList);
        clientContext.Load(listItems);
        clientContext.ExecuteQuery();

        if (listItems.Count == 1)
        {
          Microsoft.SharePoint.Client.ListItem item = listItems[0];
          fileInformation = Microsoft.SharePoint.Client.File.OpenBinaryDirect(clientContext, (string)item["FileRef"]);
          fileStream = fileInformation.Stream;
        }

      }

      return fileStream;
    }

    public static IEnumerable<eb.NamedRange> ContentControls(string url, string documentName, string libraryTitle)
    {
      IEnumerable<eb.NamedRange> contentControls = null;
      Stream fileStream = FileStream(url, documentName, libraryTitle);

      if (fileStream != null)
      {
        using (MemoryStream memoryStream = new MemoryStream())
        {
          //Copy Xml Stream. This version is tightly coupled with Excel. Future versions will decouple to support
          CopyStream(fileStream, memoryStream);

          using (WordprocessingDocument wordDocument = WordprocessingDocument.Open(memoryStream, false))
          {
            //Returns all content controls                 
            contentControls =
              wordDocument.MainDocumentPart.Document.Body.Descendants()
              .Where(c => c is SdtBlock || c is SdtRun || c is SdtCell)
              .Select(c =>
                  new eb.NamedRange
                  { 
                    Name = c.Descendants<Tag>().FirstOrDefault().Val.ToString(),
                    Value = c.InnerText,
                    ContentPart = c,
                    IsRoot = c.Parent.GetType() == typeof(Body),
                    HasTable = c.Descendants<DocumentFormat.OpenXml.Wordprocessing.Table>().FirstOrDefault() == null ? false : true,
                    IsEmbedded = c.Parent.GetType() == typeof(Paragraph)
                  }
             );
          }
        }
      }

      return contentControls;      
    }
    
    //NOTE: Passwords should never be stored as clear text. This code snippet passes the password as clear text; however,
    //the SqlConnectionStringBuilder.Encrypt property encrypts the password before sending the connection string to SQL Azure
    public static SqlConnectionStringBuilder SqlConnectionBuilder
      (string dataSource, string initialCatalog, string userName, string password, bool hosted)
    {
      SqlConnectionStringBuilder connStringBuilder;
      connStringBuilder = new SqlConnectionStringBuilder();
      connStringBuilder.DataSource = dataSource;
      connStringBuilder.InitialCatalog = initialCatalog;

      if (hosted)
      {
        //SQLAzure
        connStringBuilder.Encrypt = true;
        connStringBuilder.TrustServerCertificate = false;
      }
      
     
      //This Version: Gets UserId and Password from LoginActivity.Credentials
      connStringBuilder.UserID = userName;
      connStringBuilder.Password = password;

      return connStringBuilder;
    }

    public static SqlConnectionStringBuilder SqlConnectionBuilder(string dataSource)
    {
      SqlConnectionStringBuilder connStringBuilder;
      connStringBuilder = new SqlConnectionStringBuilder();
      connStringBuilder.DataSource = dataSource;

      //Local Test
      connStringBuilder.IntegratedSecurity = true;
    
      return connStringBuilder;
    }

    //NEXT: Use OData Feed
    public static List<eb.ListItem> ViewNames(SqlConnectionStringBuilder connStringBuilder)
    {
      using (SqlConnection connection = new SqlConnection(connStringBuilder.ToString()))
      {
        connection.Open();

        return  (from r in connection.GetSchema("TABLES").AsEnumerable()
                 where r.Field<string>("TABLE_TYPE") == "VIEW"
                 select
                 new eb.ListItem
                 {
                    Name = r.Field<string>("TABLE_NAME").Replace(" ", string.Empty),
                    Value = String.Format("{0}.{1}", r.Field<string>("table_schema"), r.Field<string>("TABLE_NAME")).Replace(" ", string.Empty)
                 }).ToList<eb.ListItem>();
      }
    }

    public static List<eb.ListItem> ViewSchema(string viewName, SqlConnectionStringBuilder connStringBuilder)
    {
      string[] splitName = viewName.Split(new char[] { '.' });
      string nonSchemaName = splitName.Length > 0 ? splitName[1] : string.Empty;

      using (SqlConnection conn = new SqlConnection(connStringBuilder.ToString()))
      {
        conn.Open();

        return (from r in conn.GetSchema("Columns").AsEnumerable()
                where r.Field<string>("TABLE_NAME") == nonSchemaName
                orderby r.Field<int>("ORDINAL_POSITION")
                select new eb.ListItem
                {
                  Name = r.Field<string>("COLUMN_NAME"),
                  Value = String.Format("{0} ({1})", r.Field<string>("DATA_TYPE"), r.Field<int?>("CHARACTER_MAXIMUM_LENGTH").ToString())
                }
                ).ToList<eb.ListItem>();
      }
    }

    //NOTE: SQL Azure does not support server.Databases
    public static List<MyData.ExpressionBuilder.Model.ListItem> DatabaseNames(SqlConnectionStringBuilder connStringBuilder)
    {
      using (SqlConnection connection = new SqlConnection(connStringBuilder.ToString()))
      {
        ServerConnection serverConn = new ServerConnection(connection);
        serverConn.Connect();

        Server sqlServer= new Server(serverConn);

        DatabaseCollection databases = sqlServer.Databases;

        //DatabaseCollection does not support AsQueryable do must foreach into List<ListItem>
        List<eb.ListItem> itemsProvider = new List<eb.ListItem>();

        foreach (Database db in databases)
        {
          itemsProvider.Add(new eb.ListItem { Name = db.Name, Value = db.Name });
        }

        return itemsProvider;
      }
    }
  }
}
